package com.aidansmeaton.weightmanager.data;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.Arrays;

import android.app.ProgressDialog;
import android.content.ContentValues;
import android.content.Context;
import android.content.res.AssetManager;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.os.AsyncTask;
import android.util.Log;
import android.widget.Toast;

import com.aidansmeaton.weightmanager.DiaryExerciseActivity;
import com.aidansmeaton.weightmanager.R;

/**
 * Copies an already existing SQLite database into this
 * application. Provides a singleton instance to a
 * database helper which provides cursors to the database.
 * @see http://www.reigndesign.com/blog/using-your-own-sqlite-database-in-android-applications/
 * 
 * @author Juan-Manuel Flux�
 * @auhor Aidan Smeaton
 */
public class DatabaseHelper extends SQLiteOpenHelper {
	
	// Table column numbers
	public static final int WEIGHT_LOG_ID = 0;
	public static final int WEIGHT_LOG_TIME = 1;
	public static final int WEIGHT_LOG_WEIGHT = 2;
	public static final int WEIGHT_LOG_COMMENT = 3;
	public static final int GOAL_LOG_ID = 0;
	public static final int GOAL_LOG_ACHIEVED = 1;
	public static final int GOAL_LOG_TYPE = 2;
	public static final int GOAL_LOG_SETDATE = 3;
	public static final int GOAL_LOG_STARTVALUE = 4;
	public static final int GOAL_LOG_CURRENTVALUE = 5;
	public static final int GOAL_LOG_ENDVALUE = 6;
	public static final int GOAL_LOG_HOW = 7;
	public static final int FOOD_LOG_ID = 0;
	public static final int FOOD_LOG_DATE = 1;
	public static final int FOOD_LOG_MEAL = 2;
	public static final int FOOD_LOG_NAME = 3;
	public static final int FOOD_LOG_MEASUREMENT = 4;
	public static final int FOOD_LOG_QUANTITY = 5;
	public static final int FOOD_LOG_NOTE = 6;
	public static final int FOOD_LOG_PHOTO = 7;
	public static final int PLATE_LOG_ID = 0;
	public static final int PLATE_LOG_FOOD_ID = 1;
	public static final int PLATE_LOG_GRADE = 2;
	public static final int PLATE_LOG_NOTE = 3;
	public static final int PLATE_LOG_CALORIES = 4;
	public static final int MOOD_LOG_ID = 0;
	public static final int MOOD_LOG_DATE = 1;
	public static final int MOOD_LOG_COMMENT = 2;
	public static final int MOOD_LOG_EMOTICON = 3;
	public static final int PHOTO_LOG_ID = 0;
	public static final int PHOTO_LOG_DATE = 1;
	public static final int PHOTO_LOG_URI = 2;
	public static final int PHOTO_LOG_COMMENT = 3;
	public static final int EXERCISE_LOG_ID = 0;
	public static final int EXERCISE_LOG_DATE = 1;
	public static final int EXERCISE_LOG_ACTIVITY = 2;
	public static final int EXERCISE_LOG_MINUTES = 3;
	public static final int EXERCISE_LOG_EXERTION = 4;
	public static final int PORTION_ID = 0;
	public static final int PORTION_TAG = 1;
	public static final int PORTION_TINY = 2;
	public static final int PORTION_SMALL = 3;
	public static final int PORTION_MEDIUM = 4;
	public static final int PORTION_LARGE = 5;
	
	// Android's default system path of your application database.
    private static final String DB_PATH = "/data/data/com.aidansmeaton.weightmanager/databases/";
    private static final String DB_NAME = "weightmanager.db";
    private static DatabaseHelper instance;
    private static boolean exists;
    private SQLiteDatabase db;
    private final Context context;
    
    /**
     * Constructor
     */
    private DatabaseHelper(Context context) {
            super(context, DB_NAME, null, 1);
            this.context = context;
            
            exists = checkDatabaseExists();         
    }
    
    /**
     * Returns the singleton DatabaseHelper, instantiating
     * if not yet done so.
     * @param context Application context.
     * @return The DatabaseHelper singleton.
     */
    public static DatabaseHelper getInstance(Context context) {
            if (instance == null) {
                    instance = new DatabaseHelper(context);
                    try {
                            instance.createDatabase();
                    } catch (IOException ioe) {
                            /* Critical error - database could not be created. This
                             * is probably due to lack of space.
                             */
                            throw new Error("Unable to create database, probably due to lack of space");
                    }
                    
                    if (!exists) {
                            return null;
                    }
            }
            
            return instance;
    }
    
    /**
     * Creates an empty database on the system and
     * populates it from a pre-prepared database.
     */
    private void createDatabase() throws IOException{
            if(checkDatabaseExists()){
                    /* Database exists, so we can just open it */
                    instance.openDatabase();
            } else {
                    /* Database does not exist - create a new one */
                    this.getReadableDatabase();
                    
                    /* Set up an AsyncTask to go off and copy our
                     * pre-prepared database into the data
                     * section of this application.
                     */
                    copyDatabase();
                    exists = true;
                    instance.openDatabase();
            }
    }

    /**
     * Checks if the database already exists.
     */
    public static boolean checkDatabaseExists() {
            SQLiteDatabase checkDB = null;

            /* Opening the database will fail if it doesn't
             * exist, so we can catch and ignore that error.
             */
            try {
                    String myPath = DB_PATH + DB_NAME;
                    checkDB = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);
            } catch (SQLiteException e) {
                    /* Database does't exist yet - we can
                     * ignore this exception. This function
                     * is not for creating the database.
                     */
            }

            if (checkDB != null) {
                    checkDB.close();
            }

            return checkDB != null ? true : false;
    }

    /**
     * Copies each database file assets-folder to a single file
     * in the data storage for this app.
     * 
     * @see http://stackoverflow.com/questions/2860157/load-files-bigger-than-1m-from-assets-folder/3093966#3093966
     * @author Seva Alekseyev
     */
    private void copyDatabase() throws IOException{
            File dbFile = new File(DB_PATH + DB_NAME);
            AssetManager am = context.getAssets();
            OutputStream os = new FileOutputStream(dbFile);
            dbFile.createNewFile();
            
            byte[] b = new byte[1024];
            int i, r;
            
            String[] Files = am.list("");
            Arrays.sort(Files);
            
            for (i = 1; i < 10; i++) {
                    String fn = String.format("%d.db", i);
                    if (Arrays.binarySearch(Files, fn) < 0)
                            break;
                    InputStream is = am.open(fn);
                    while ((r = is.read(b)) != -1)
                            os.write(b, 0, r);
                    is.close();
            }
            os.close();
    }
    
    
    /**
     * Attempts to open the database in read-write mode.
     */
    private void openDatabase() throws SQLException{
            String myPath = DB_PATH + DB_NAME;
            db = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READWRITE);
    }

    /**
     * Close the open database.
     */
    @Override
    public synchronized void close() {
            if (db != null)
                    db.close();

            super.close();
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
            // stub if needed
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            // stub if needed
    }
    
            
    /* AsyncTask to create database on first run. */
    private class CreateDatabaseTask extends AsyncTask<Integer, Integer, Boolean> {
            ProgressDialog dialog;
            
            @Override
            protected void onPreExecute() {
                    dialog = new ProgressDialog(context);
                    dialog.setTitle(context.getString(R.string.database_prepare));
                    dialog.setMessage(context.getString(R.string.database_prepare_message));
                    dialog.setIndeterminate(true);
                    dialog.setCancelable(false);
            }
            
            @Override
            protected Boolean doInBackground(Integer... args) {
                    try {
                            copyDatabase();
                    } catch (IOException e) {
                            /* An error occurred while copying the database */
                            e.printStackTrace();
                    }
                    return true;
            }
            
            @Override
            protected void onPostExecute(Boolean result) {
                    try {
                            dialog.dismiss();
                    } catch (IllegalArgumentException e) {
                            /* See Ticket #11. Appears to throw this error
                             * if the dialog somehow loses its parent view.
                             * Can safely be ignored in this case, we're only
                             * hiding a dialog window.
                             */
                    }
                    exists = true;
                    instance.openDatabase();
            }
    }
	
	
	
	// Queries
	
	public Cursor getWeightLogs() {
		return db.rawQuery(
				"SELECT * FROM weight_log ORDER BY time DESC"
				, null);
	}
	
	public Cursor getWeightLogs(boolean ascending) {
		if (!ascending) return getWeightLogs();
		else return db.rawQuery(
				"SELECT * FROM weight_log ORDER BY time ASC"
				, null);
	}
	
	public Cursor getWeightLogsBeforeDate(String date) {
		return db.rawQuery(
				"SELECT * FROM weight_log WHERE date(time) <= '"+date+"' ORDER BY time DESC"
				, null);
	}
	
	public Cursor getWeightLogsForDate(String date) {
		return db.rawQuery(
				"SELECT * FROM weight_log WHERE date(time) = '"+date+"'"
				, null);
	}	
	
	public Cursor getGoalLogs() {
		return db.rawQuery(
				"SELECT * FROM goal_log"
				, null);
	}

	public Cursor getFoodLogsForDate(String date) {
		return db.rawQuery(
				"SELECT * FROM food_log WHERE date(date) = '"+date+"'"
				, null);
	}

	public Cursor getPlateLogs(int food_id) {
		return db.rawQuery(
				"SELECT * FROM plate_log WHERE food_log_id = "+food_id
				, null);
	}

	public Cursor getPlateLog(int plate_id) {
		return db.rawQuery(
				"SELECT * FROM plate_log WHERE _id = "+plate_id
				, null);
	}

	public long addFoodLog(ContentValues cv) {
		return db.insert("food_log", null, cv);
	}
	
	public long editFoodLog(ContentValues cv) {
		return db.replace("food_log", null, cv);
	}

	public long addPlateLog(ContentValues cv) {
		return db.insert("plate_log", null, cv);
	}
	
	public long deletePlateLogFromID(int id) {
		return db.delete("plate_log", "_id = "+id, null);
	}
	
	public long deletePlateLogsFromFoodID(int id) {
		return db.delete("plate_log", "food_log_id = "+id, null);
	}
	
	public Cursor getFoodLogs() {
		return db.rawQuery(
				"SELECT * FROM food_log ORDER BY date DESC"
				, null);
	}
	
	public int deleteFoodLog(int id) {
		return db.delete("food_log", "_id = "+id, null) +
			db.delete("plate_log", "food_log_id = "+id, null);
	}

	public Cursor getFoodLog(int food_id) {
		return db.rawQuery(
				"SELECT * FROM food_log WHERE _id = "+food_id
				, null);
	}
	
	public Cursor getPlateLogsFromDate(String date){
		return db.rawQuery(
				"SELECT * FROM plate_log p, food_log f WHERE date(f.date) = '"+date+"' " +
				"AND p.food_log_id = f._id"
				, null);
	}

	public long editWeightLog(String time, int weight, String comment) {
		ContentValues cv = new ContentValues();
    	cv.putNull("_id");
    	cv.put("time", time);
    	cv.put("weight", weight);
    	if (comment != null) cv.put("comment", comment); else cv.putNull("comment");
    	db.delete("weight_log", "time = ?", new String[] { time });
		return db.insert("weight_log", null, cv);
	}

	public int removeWeightLogsForDate(String date) {
		return db.delete("weight_log", "time LIKE '"+date+"'", null);
	}
	
	public long addMoodLog(String date, String comment, int emoticon) {
		ContentValues cv = new ContentValues();
    	cv.putNull("_id");
    	cv.put("date", date);
    	cv.put("comment", comment);
    	cv.put("emoticon", emoticon);
		return db.insert("mood_log", null, cv);
	}
	
	public long editMoodLog(int id, String date, String comment, int emoticon) {
		ContentValues cv = new ContentValues();
    	cv.put("_id",id);
    	cv.put("date", date);
    	cv.put("comment", comment);
    	cv.put("emoticon", emoticon);
		return db.replace("mood_log", null, cv);
	}
	
	public Cursor getMoodLogs(String date) {
		return db.rawQuery(
				"SELECT * FROM mood_log WHERE date(date) = '"+date+"' ORDER BY date DESC"
				, null);
	}
	
	public int removeMoodLog(int id) {
		return db.delete("mood_log", "_id = "+id, null);
	}

	public Cursor getMoodLog(int mood_id) {
		return db.rawQuery(
				"SELECT * FROM mood_log WHERE _id = "+mood_id
				, null);
	}
	
	public long addPhotoLog(String date, String uri, String comment) {
		ContentValues cv = new ContentValues();
    	cv.putNull("_id");
    	cv.put("date", date);
    	cv.put("uri", uri);
    	cv.put("comment", comment);
		return db.insert("photo_log", null, cv);
	}
	
	public Cursor getPhotoLogs(String date) {
		return db.rawQuery(
				"SELECT * FROM photo_log WHERE date(date) = '"+date+"' ORDER BY date DESC"
				, null);
	}

	public long addExerciseLog(String date, String activity, int minutes, int exertion) {
		ContentValues cv = new ContentValues();
    	cv.putNull("_id");
    	cv.put("date", date);
    	cv.put("activity", activity);
    	cv.put("minutes", minutes);
    	if (exertion != -1) cv.put("exertion", exertion); else cv.putNull("exertion");
		return db.insert("exercise_log", null, cv);
	}
	
	public long editExerciseLog(int id, String date, String activity, int minutes, int exertion) {
		ContentValues cv = new ContentValues();
    	cv.put("_id", id);
    	cv.put("date", date);
    	cv.put("activity", activity);
    	cv.put("minutes", minutes);
    	if (exertion != -1) cv.put("exertion", exertion); else cv.putNull("exertion");
		return db.replace("exercise_log", null, cv);
	}

	public Cursor getExerciseLogsForDate(String date) {
		return db.rawQuery(
				"SELECT * FROM exercise_log WHERE date(date) = '"+date+"'"
				, null);
	}

	public int removeExerciseLog(int id) {
		return db.delete("exercise_log", "_id = "+id, null);
	}

	public Cursor getExerciseLog(int id) {
		return db.rawQuery(
				"SELECT * FROM exercise_log WHERE _id = "+id
				, null);
	}

	public Cursor getExerciseLogsBetweenDates(String startDate, String endDate) {
		return db.rawQuery(
				"SELECT * FROM exercise_log WHERE date >= '"+startDate+"' AND date <= '"+endDate+"' ORDER BY date DESC"
				, null);
	}
	
	public Cursor getPortionWithTag(String tag) {
		return db.rawQuery(
				"SELECT * FROM portion WHERE tag = '"+tag+"'"
				, null);
	}

	public Cursor getFoodLogsWithPhoto(String photoPath) {
		return db.rawQuery(
				"SELECT * FROM food_log WHERE photo = '"+photoPath+"'"
				, null);
	}
	
	public long editFoodLog(int id, String date, String name, String note, String photo) {
		ContentValues cv = new ContentValues();
    	cv.put("_id", id);
    	if (date != null) cv.put("date", date); else cv.putNull("date");
    	cv.putNull("meal");
    	if (name != null) cv.put("name", name); else cv.putNull("name");
    	cv.putNull("measurement");
    	cv.putNull("quantity");
    	cv.put("note", note);
    	cv.put("photo", photo);
		return db.replace("food_log", null, cv);
	}

	public int deletePhotoLogs(String date) {
		return db.delete("photo_log", "date(date) = '"+date+"'", null);
	}
}